package cn.edu.buaa.cnsatm.traffic_big_data_server.waterway.dao;

import cn.edu.buaa.cnsatm.traffic_big_data_server.integrated.model.RoadStatusModel;
import cn.edu.buaa.cnsatm.traffic_big_data_server.integrated.dao.RoadStatusDAO;
import cn.edu.buaa.cnsatm.traffic_big_data_server.util.JDBCUtil;
import cn.edu.buaa.cnsatm.traffic_big_data_server.util.WebSpiderUtil;
import cn.edu.buaa.cnsatm.traffic_big_data_server.waterway.model.SeaStatusModel;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;

import javax.xml.transform.Result;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.logging.SimpleFormatter;

public class SeaStatusDAO {

    public static void main(String args[]) throws ParseException, SQLException, IOException {
        JDBCUtil.startConnection();
        //getSeaBlockFromWebAndSaveToDatabase();
    }
    public static ResultSet getTopKWaveHeight(Integer k, String currentTime){
        /**
         * @author: LX
         * @methodName: getTopKWaveHeight
         * @description: 从离线数据库中获取离当前时间最近的，浪最高的K个海域的名字、浪高、浪高等级
         * @data: 2019-10-19
         **/
        String sql = "SELECT DISTINCT sea_area_name, wave_height, start_time, wave_height_level, abs(date_part('epoch',start_time::timestamp - '" + currentTime + "'::timestamp)) " +
                " FROM sea_status ORDER BY abs(date_part('epoch',start_time::timestamp - '" + currentTime + "'::timestamp)), wave_height DESC limit " + k.toString();
        //System.out.println(sql);
        Statement stmt = null;
        ResultSet resultSet = null;
        try {
            stmt = JDBCUtil.databaseConnecttion.createStatement();//创建数据库查询实体
            resultSet= stmt.executeQuery(sql);//执行Sql查询语句
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return resultSet;
    }
    public static ResultSet getSeaStatusByCellId(String cellId){
        /**
         * @author: LX
         * @methodName: getSeaStatusByCellId
         * @description: 根据格子id获取浪高等信息
         * @data: 2019-12-2
         **/
        String sql = "SELECT wave_height, time from wave where cell_id = '" + cellId +
                "' and time = (select max(time) from wave where cell_id = '" + cellId +"')";
        Statement stmt = null;
        ResultSet resultSet = null;
        //System.out.println(sql);
        try {
            stmt = JDBCUtil.databaseConnecttion.createStatement();//创建数据库查询实体
            resultSet= stmt.executeQuery(sql);//执行Sql查询语句
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return resultSet;
    }
    public static ResultSet getAllSeaCell(){
        /**
         * @author: LX
         * @methodName: getAllSeaStatusCell
         * @description: 从离线数据库中获取离当前时间最近的，每个海域格子
         * @data: 2019-12-2
         **/
        String sql = "SELECT cell_id, detail from cell ";
        Statement stmt = null;
        ResultSet resultSet = null;
        //System.out.println(sql);
        try {
            stmt = JDBCUtil.databaseConnecttion.createStatement();//创建数据库查询实体
            resultSet= stmt.executeQuery(sql);//执行Sql查询语句
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return resultSet;
    }
    public static Integer updateSeaStatus(SeaStatusModel seaStatusModel) throws SQLException {
        /**
         * @author: LX
         * @methodName: updateSeaStatus
         * @description: 根据主键（海域、时间）更新海况数据
         * @data: 2019-10-20
         **/
        int updateNum = 0;
        String sql = "UPDATE sea_status SET wave_height = " + seaStatusModel.getWave_height().toString() +
                ", wave_height_level = " + seaStatusModel.getWave_height_level().toString() +
                " WHERE sea_area_name = '" + seaStatusModel.getSea_area_name() +
                "' AND start_time = '" + seaStatusModel.getStart_time() + "'";
        //System.out.println(sql);
        Statement stmt = null;
        try {
            stmt = JDBCUtil.databaseConnecttion.createStatement();//创建数据库查询实体
            updateNum = stmt.executeUpdate(sql);//执行Sql查询语句
            //System.out.print(insertNum);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            stmt.close();
        }
        return updateNum;
    }
    public static String insertSeaStatus(SeaStatusModel seaStatusModel) throws SQLException {
        /**
         * @author: LX
         * @methodName: insertSeaStatus
         * @description: 将海况想应得数据插入到sea_status表中
         * @data: 2019-10-19
         **/
        int insertNum = 0;
        String sql = "INSERT into sea_status (sea_area_name, wave_height, wave_direction, wave_height_level, cycle, sea_temp, start_time) " +
                "values('" + seaStatusModel.getSea_area_name() + "', " + seaStatusModel.getWave_height().toString() + ", "  +
                seaStatusModel.getWave_direction().toString() + "," + seaStatusModel.getWave_height_level().toString() + "," +
                seaStatusModel.getCycle().toString() + "," + seaStatusModel.getSea_temp().toString() + ",'" + seaStatusModel.getStart_time() + "' );";
        //System.out.println(sql);
//        Statement stmt = null;
//        try {
//            stmt = JDBCUtil.databaseConnecttion.createStatement();//创建数据库查询实体
//            insertNum = stmt.executeUpdate(sql);//执行Sql查询语句
//            stmt.close();
//            //System.out.print(insertNum);
//        } catch (SQLException e) {
//            //e.printStackTrace();
//        }
        return sql;
    }

}
